<?php

class Attendence_model extends CI_Model {

    /**
    * Validate the login's data with the database
    * @param string $user_name
    * @param string $password
    * @return void
    */

    public function get_attendence($academic_session=null,$group_id=null, $today=null, $search_string=null, $order=null, $order_type='Asc', $limit_start, $limit_end)
    {
    	
    	$sql="select x.EMP_ID,DATE_FORMAT(x.date_time,'%b %d %Y %h:%i %p') date_time, x.in_out, y.student_name
			from attendance x,student y
			where x.EMP_ID=y.student_id
			and DATE_FORMAT(x.date_time,('%d-%m-%Y'))='".$today."'";
    	$query=$this->db->query($sql);
    	return $query->result_array();
    	
	    /*
		$this->db->select("student_id,group_id,student_name,fathername,mothername,DATE_FORMAT(dob,('%d-%m-%Y')) dob,religion,bloodgroup,academic_session,remarks");
		$this->db->from('student');
		if($academic_session != null && $academic_session != 0){
		    $this->db->where('academic_session', $academic_session);
		}
		if($group_id!=null && $group_id!='')
		{
			$this->db->where('group_id', $group_id);
		}
		if($search_string){
			//$like = "student_id like '%".$search_string."%' OR student_name like '%".$search_string."%' OR fathername like '%".$search_string."%' OR mothername like '%".$search_string."%'";
			//$this->db->where($like);
			$this->db->like('student_id', $search_string);
			$this->db->or_like('student_name', $search_string);
			$this->db->or_like('fathername', $search_string);
			$this->db->or_like('mothername', $search_string);
			
		}

		//$this->db->group_by('student_id');

		if($order){
			$this->db->order_by($order, $order_type);
		}else{
		    $this->db->order_by('student_id', $order_type);
		}


		$this->db->limit($limit_start, $limit_end);
		//$this->db->limit('4', '4');


		$query = $this->db->get();
		
		return $query->result_array();
	    //echo $query->num_rows();   
	     * */
	     
    }
    function get_students_with_option($academic_session,$group_id,$limit_start,$limit_end)
    {
    	$this->db->select("student_id,group_id,student_name,fathername,mothername,sectionname,DATE_FORMAT(dob,('%d-%m-%Y')) dob,religion,bloodgroup,academic_session");
    	$this->db->from('student');
    	$this->db->where('academic_session', $academic_session);
    	$this->db->where('group_id', $group_id);
    	$this->db->order_by('student_id');
    	$this->db->limit($limit_start, $limit_end);
    	$query = $this->db->get();
    	 
    	return $query->result_array();
    }
    /**
     * Count the number of rows
     * @param int $manufacture_id
     * @param int $search_string
     * @param int $order
     * @return int
     */
    
   
    //////////////////////////////////////////////////
    function count_attendence($academic_session=null,$group_id=null, $search_string=null, $order=null)
    {
    	$this->db->select('*');
    	$this->db->from('student');
    	if($academic_session != null && $academic_session != 0){
    		$this->db->where('academic_session', $academic_session);
    	}
    	if($group_id != null && $group_id != ''){
    		$this->db->where('group_id', $group_id);
    	}
    	if($search_string){
    		$this->db->like('student_name', $search_string);
    	}
    	if($order){
    		$this->db->order_by($order, 'Asc');
    	}else{
    		$this->db->order_by('student_id', 'Asc');
    	}
    	$query = $this->db->get();
    	return $query->num_rows();
    }
    
    //////////////////////////////////
    public function get_absentee($academic_session=null,$group_id=null, $today=null, $search_string=null, $order=null, $order_type='Asc', $limit_start, $limit_end)
    {
    
    	$sql="Select student_id, student_name, fathername, mothername, dob, group_id, remarks
              from student where 
    			student_id not in (
    			select EMP_ID from attendance where  DATE_FORMAT(date_time,('%d-%m-%Y')) ='".$today."')";
    	$query=$this->db->query($sql);
    	return $query->result_array();
    	 
    
    }
  ////////////////////////////////
    function count_absentee($academic_session=null,$group_id=null, $today=null, $search_string=null, $order=null, $order_type='Asc', $limit_start, $limit_end)
    {
    	$sql="Select student_id, student_name, fathername, mothername, dob, group_id, remarks
              from student where 
    			student_id not in (
    			select EMP_ID from attendance where  DATE_FORMAT(date_time,('%d-%m-%Y')) ='".$today."')";
    	$query=$this->db->query($sql);
    	return $query->num_rows();
    }
    ////////////////////////////////////////////////////////
    
    function get_emp_attendence($dept_id=null, $start_date=null, $end_date=null, $search_string=null, $order=null, $limit_start, $limit_end)
    {
    	 
    	$search_string=trim($search_string);
    	$sql="select x.EMP_ID, DATE_FORMAT(x.date_time,'%b %d %Y %h:%i %p') date_time, x.in_out, y.name,  
			y.dept_id, x.comments from attendance x,employee y
			where x.EMP_ID=y.EMP_ID
			and DATE_FORMAT(x.date_time,('%d/%m/%Y'))>='".$start_date."'
            and  DATE_FORMAT(x.date_time,('%d/%m/%Y'))<= '".$end_date."'";
    	
    	if($dept_id!=null && $dept_id!=" ")
    		$sql=$sql." and y.dept_id= '".$dept_id."'";
    	
    	if($search_string!=null && $search_string!=" ")
    		$sql=$sql." and y.name like '%".$search_string."%'";
        if($order!=null && $order!=" ")
    	   $sql=$sql." Order by ".$order;
    	$query=$this->db->query($sql);
    	return $query->result_array();
    	 
    	/*
    	 $this->db->select('*');
    	$this->db->from('student');
    	if($academic_session != null && $academic_session != 0){
    	$this->db->where('academic_session', $academic_session);
    	}
    	if($group_id != null && $group_id != ''){
    	$this->db->where('group_id', $group_id);
    	}
    	if($search_string){
    	$this->db->like('student_name', $search_string);
    	}
    	if($order){
    	$this->db->order_by($order, 'Asc');
    	}else{
    	$this->db->order_by('student_id', 'Asc');
    	}
    	$query = $this->db->get();
    	return $query->num_rows();
    	*/
    }
    
    //////////////////////////////////////////////////
    function count_emp_attendence($dept_id=null, $start_date=null, $end_date=null, $search_string=null, $order=null, $limit_start, $limit_end)
    {
    	$search_string=trim($search_string);
    	$sql="select x.EMP_ID, DATE_FORMAT(x.date_time,'%b %d %Y %h:%i %p') date_time, x.in_out, y.name 
			from attendance x,employee y
			where x.EMP_ID=y.EMP_ID
			and DATE_FORMAT(x.date_time,('%d/%m/%Y'))>='".$start_date."'
            and  DATE_FORMAT(x.date_time,('%d/%m/%Y'))<= '".$end_date."'";
    	
    	if($dept_id!=null && $dept_id!=" ")
    		$sql=$sql." and y.dept_id= '".$dept_id."'";
    	
    	if($search_string!=null && $search_string!=" ")
    		$sql=$sql." and y.name like '%".$search_string."%'";
    	
    	$query=$this->db->query($sql);
    	return $query->num_rows();
    	
    	/*
    	$this->db->select('*');
    	$this->db->from('student');
    	if($academic_session != null && $academic_session != 0){
    		$this->db->where('academic_session', $academic_session);
    	}
    	if($group_id != null && $group_id != ''){
    		$this->db->where('group_id', $group_id);
    	}
    	if($search_string){
    		$this->db->like('student_name', $search_string);
    	}
    	if($order){
    		$this->db->order_by($order, 'Asc');
    	}else{
    		$this->db->order_by('student_id', 'Asc');
    	}
    	$query = $this->db->get();
    	return $query->num_rows();
    	*/
    }
/////////////////////////////////////////////////////////
	
}

